﻿package jakey.SalaryCount.SQLite;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class dbHelper extends SQLiteOpenHelper {
	
	private final static int DATABASE_VERSION = 1;
	private final static String TABLE_NAME = "user";
	private final static String FIELD_ID="_id"; 
	private final static String FIELD_USER_NAME = "user_name";
	private final static String FIELD_SALARY = "salary";
	private final static String FIELD_MAJOR = "major";
	private final static String FIELD_INFO = "info";
	
	public dbHelper(Context context, String name, CursorFactory cursorFactory) {
		super(context, name, cursorFactory, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String sql = "CREATE TABLE " + TABLE_NAME + "(_id INTEGER PRIMARY KEY AUTOINCREMENT, user_name varchar(20), salary int, major varchar(20), info varchar(200) )";
		db.execSQL(sql);
		sql = "CREATE TABLE baseinfo (_id INTEGER PRIMARY KEY AUTOINCREMENT, lv1 INT, lv2 INT,lv3 INT,lv4 INT )";
		db.execSQL(sql);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		String sql = " DROP TABLE IF EXISTS " + TABLE_NAME;
		db.execSQL(sql);
		onCreate(db);
	}

	public Cursor select(String table, String[] columns, String where, String[] whereValue, String groupBy, String having, String orderBy) {
		SQLiteDatabase db = this.getReadableDatabase();
		if(table == null) table = TABLE_NAME;
		Cursor cursor = db.query(table, columns, where, whereValue, groupBy, having, orderBy);
		return cursor;
	}

	public long insert(String user_name, int salary, String major, String info) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(FIELD_USER_NAME, user_name);
		cv.put(FIELD_SALARY, salary);
		cv.put(FIELD_MAJOR, major);
		if(info != null) cv.put(FIELD_INFO, info);
		long row = db.insert(TABLE_NAME, null, cv);
		return row;
	}

	public int delete(String where, String[] whereValue) {
		SQLiteDatabase db = this.getWritableDatabase();
		return db.delete(TABLE_NAME, where, whereValue);
	}

	public void update(int id, String user_name, int salary, String major, String info) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = FIELD_ID + "=?";
		String[] whereValue = { Integer.toString(id) };
		ContentValues cv = new ContentValues();
		cv.put(FIELD_USER_NAME, user_name);
		cv.put(FIELD_SALARY, salary);
		cv.put(FIELD_MAJOR, major);
		cv.put(FIELD_INFO, info);
		db.update(TABLE_NAME, cv, where, whereValue);
	}
	
	public void exec(String sql) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL(sql);
	}

}
